package com.example.controller.aboutStudent;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.crypto.SecureUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.example.vo.request.*;
import com.example.vo.response.CollegeMajorProvinceDTO;
import com.example.vo.response.FileLoadError;
import com.example.vo.response.StudentMoreInfoDTO;
import com.example.permission.PermissionCheck;
import com.example.pojo.aboutProvince.CollegeMajorProvince;
import com.example.pojo.aboutStudent.Student;
import com.example.service.aboutCollege.CollegeEnrollmentService;
import com.example.service.aboutProvince.CollegeMajorProvinceService;
import com.example.service.aboutStudent.StudentMoreInfoService;
import com.example.service.aboutStudent.StudentService;
import com.example.vo.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

import static com.example.utils.SystemConstants.PHONE_ENCRYPT_KEY;

@RestController
@RequestMapping("/file")
@Slf4j
public class FileController {

    @Resource
    private StudentService studentService;

    @Resource
    private StudentMoreInfoService studentMoreInfoService;

    @Resource
    private CollegeEnrollmentService collegeEnrollmentService;

    @Resource
    private CollegeMajorProvinceService collegeMajorProvinceService;


    @PostMapping("/uploadFileToSaveStudents")
    @PermissionCheck(permissionPath = "/studentDataManagement/addStudent")
    public Result uploadFileToSaveStudents(@RequestParam MultipartFile file) {
        // 添加失败
        List<FileLoadError> errorRow = new ArrayList<>();

        if (!file.isEmpty()) {
            // 创建工作簿对象
            Workbook workbook;

            try {
                workbook = WorkbookFactory.create(file.getInputStream());
            } catch (IOException e) {
                throw new RuntimeException(e);
            }

            // 获取工作表对象--0代表第一个工作表
            Sheet sheet = workbook.getSheetAt(0);

            this.constructOperateStudent(sheet, errorRow);


        } else {
            return Result.error("解析文件失败");
        }

        return Result.success(errorRow);
    }

    private void constructOperateStudent(Sheet sheet, List<FileLoadError> errorRow) {
        // 获取行对象
        Row rowHeader = sheet.getRow(0);

        // 创建一个数据格式化器，用于将所有单元格的值转换为字符串
        DataFormatter formatter = new DataFormatter();

        // 从第二行开始，第一行是表头
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);

            if (row == null) {
                // 处理空行
                continue;
            }

            OperateStudentForm student = new OperateStudentForm();

            try {
                // 处理每一个格子的值，通过判断表头的内容来进行赋值
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    String value = formatter.formatCellValue(row.getCell(j));

                    // 如果单元格是空的，则跳过
                    if (Objects.equals(value, "")) {
                        continue;
                    }
                    String headerValue = rowHeader.getCell(j).getStringCellValue();

                    switch (headerValue) {
                        case "姓名":
                            student.setName(value);
                            break;
                        case "学号":
                            student.setStuId(value);
                            break;
                        case "班级":
                            student.setClassN(value);
                            break;
                        case "总分":
                            student.setTotalPoints(Double.parseDouble(value));
                            break;
                        case "学院":
                            student.setCollege(value);
                            break;
                        case "生源地":
                            value = value.replaceAll("省|市", "");
                            student.setOriginArea(value);
                            break;
                        case "录取专业":
                            student.setMajor(value);
                            break;
                        case "性别":
                            student.setSex(value.equals("男") ? 1 : 0);
                            break;
                        case "年级":
                            value = value.replaceAll("[^0-9]", "");
                            student.setGrade(value);
                            break;
                        case "联系电话":
                            student.setTelephone(value);
                            break;
                        case "批次":
                            student.setBatch(value);
                            break;
                        case "招生中学":
                            student.setMiddleSchool(value);
                            break;
                        case "第一志愿":
                            student.setFirstChoice(value);
                            break;
                        case "第二志愿":
                            student.setSecondChoice(value);
                            break;
                        case "第三志愿":
                            student.setThirdChoice(value);
                            break;
                        case "第四志愿":
                            student.setFourthChoice(value);
                            break;
                        case "第五志愿":
                            student.setFifthChoice(value);
                            break;
                        case "出生日期":
                            String formattedDate = value.substring(0, 4) + "-" + value.substring(4, 6) + "-" + value.substring(6);
                            student.setBirthday(LocalDate.parse(formattedDate, DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                            break;
                        case "民族":
                            student.setEthnic(value);
                            break;
                        case "政治面貌":
                            student.setPolitical(value);
                            break;
                        case "身份证号":
                            student.setIdNumber(value);
                            break;
                        case "身份证类型":
                            student.setIdType(value);
                            break;
                        case "出生地":
                            student.setBirthplace(value);
                            break;
                        case "籍贯":
                            student.setNativePlace(value);
                            break;
                        case "国家":
                            student.setCountry(value);
                            break;
                        case "入学年月":
                            String formattedDate2 = value.substring(0, 4) + "-" + value.substring(4, 6) + "-" + value.substring(6);
                            student.setEnrollmentDate(LocalDate.parse(formattedDate2, DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                            break;
                        case "培养方式":
                            student.setTrainingMode(value);
                            break;
                        case "学制":
                            student.setAcademicYear(Integer.parseInt(value));
                            break;
                        case "外语语种":
                            student.setForeignLang(value);
                            break;
                        case "语文成绩":
                            student.setChineseScore(BigDecimal.valueOf(Double.parseDouble(value)));
                            break;
                        case "数学成绩":
                            student.setMathScore(BigDecimal.valueOf(Double.parseDouble(value)));
                            break;
                        case "外语成绩":
                            student.setForeignScore(BigDecimal.valueOf(Double.parseDouble(value)));
                            break;
                        case "综合成绩":
                            student.setComprehensiveScore(BigDecimal.valueOf(Double.parseDouble(value)));
                            break;
                        default:
                            // 对于未知的列名，可以选择忽略或者输出警告信息
                            System.out.println("Unknown column name: " + value);
                            break;
                    }
                }

                studentService.saveStudent(student);

                collegeEnrollmentService.updateCollegeData(student.getCollege(), student.getGrade());
            } catch (Exception e) {

                FileLoadError fileLoadError = new FileLoadError();
                fileLoadError.setRow(i + 1);

                if (e instanceof DuplicateKeyException) {
                    // 处理 SQLIntegrityConstraintViolationException 异常
                    fileLoadError.setErrMessage("重复添加");
                } else {
                    log.error(e.getMessage());
                    // 处理其他异常
                    fileLoadError.setErrMessage("行内字段错误");
                }

                errorRow.add(fileLoadError);
            }
        }

    }


    @PostMapping("/uploadFileToSaveProvinces")
    public Result uploadFileToSaveProvinces(@RequestParam MultipartFile file) {
        // 添加失败
        List<FileLoadError> errorRow = new ArrayList<>();

        if (!file.isEmpty()) {
            // 创建工作簿对象
            Workbook workbook;

            try {
                workbook = WorkbookFactory.create(file.getInputStream());
            } catch (IOException e) {
                throw new RuntimeException(e);
            }

            // 获取工作表对象--0代表第一个工作表
            Sheet sheet = workbook.getSheetAt(0);

            this.constructOperateProvinces(sheet, errorRow);

        } else {
            return Result.error("解析文件失败");
        }

        return Result.success(errorRow);
    }

    private void constructOperateProvinces(Sheet sheet, List<FileLoadError> errorRow) {
        // 获取行对象
        Row rowHeader = sheet.getRow(0);

        // 创建一个数据格式化器，用于将所有单元格的值转换为字符串
        DataFormatter formatter = new DataFormatter();

        // 从第二行开始，第一行是表头
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);

            if (row == null) {
                // 处理空行
                continue;
            }

            CollegeMajorProvince collegeMajorProvince = new CollegeMajorProvince();

            try {
                // 处理每一个格子的值，通过判断表头的内容来进行赋值
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    String value = formatter.formatCellValue(row.getCell(j));

                    // 如果单元格是空的，则跳过
                    if (Objects.equals(value, "")) {
                        continue;
                    }

                    String headerValue = rowHeader.getCell(j).getStringCellValue();

                    switch (headerValue) {
                        case "省份":
                            collegeMajorProvince.setProvince(value);
                            break;
                        case "一本线分数":
                            collegeMajorProvince.setAdmissionScore(Integer.parseInt(value));
                            break;
                        case "年份":
                            collegeMajorProvince.setGrade(value);
                            break;
                        default:
                            // 对于未知的列名，可以选择忽略或者输出警告信息
                            System.out.println("Unknown column name: " + value);
                            break;
                    }
                }

                LambdaQueryWrapper<CollegeMajorProvince> collegeMajorProvinceLambdaQueryWrapper = new LambdaQueryWrapper<>();
                collegeMajorProvinceLambdaQueryWrapper.eq(CollegeMajorProvince::getProvince, collegeMajorProvince.getProvince())
                        .eq(CollegeMajorProvince::getGrade, collegeMajorProvince.getGrade());
                CollegeMajorProvince one = collegeMajorProvinceService.getOne(collegeMajorProvinceLambdaQueryWrapper);

                if (one == null) {
                    collegeMajorProvinceService.save(collegeMajorProvince);
                } else {
                    LambdaUpdateWrapper<CollegeMajorProvince> collegeMajorProvinceLambdaUpdateWrapper = new LambdaUpdateWrapper<>();
                    collegeMajorProvinceLambdaUpdateWrapper.eq(CollegeMajorProvince::getProvince, collegeMajorProvince.getProvince())
                            .eq(CollegeMajorProvince::getGrade, collegeMajorProvince.getGrade())
                            .set(CollegeMajorProvince::getAdmissionScore, collegeMajorProvince.getAdmissionScore());
                    collegeMajorProvinceService.update(collegeMajorProvinceLambdaUpdateWrapper);
                }

            } catch (Exception e) {

                FileLoadError fileLoadError = new FileLoadError();
                fileLoadError.setRow(i + 1);

                if (e instanceof DuplicateKeyException) {
                    fileLoadError.setErrMessage("重复添加");
                } else {
                    log.error(e.getMessage());
                    // 处理其他异常
                    fileLoadError.setErrMessage("行内字段错误");
                }

                errorRow.add(fileLoadError);
            }

        }

    }

    @GetMapping("/downloadTemplateFile")
    public ResponseEntity<ByteArrayResource> downloadFile() throws IOException {
        String filePath = "src/main/resources/templates/template.xlsx";
        InputStream inputStream = FileController.class.getClassLoader().getResourceAsStream("templates/template.xlsx");

        File file = null;

        if (inputStream != null) {
            try {
                // 创建一个临时文件
                File tempFile = File.createTempFile("tempFile", ".xlsx");

                // 将 InputStream 中的数据写入临时文件
                try (FileOutputStream outputStream = new FileOutputStream(tempFile)) {
                    byte[] buffer = new byte[1024];
                    int bytesRead;
                    while ((bytesRead = inputStream.read(buffer)) != -1) {
                        outputStream.write(buffer, 0, bytesRead);
                    }
                }

                // 现在，tempFile 变量包含了从 InputStream 中读取的文件数据
                file = tempFile;
            } catch (IOException e) {
                // 处理异常
            }
        }

        // 从服务器读取文件
//        File file = new File(url.getFile());


        byte[] data = Files.readAllBytes(file.toPath());
        ByteArrayResource resource = new ByteArrayResource(data);

        // 设置响应头信息
        HttpHeaders headers = new HttpHeaders();
        headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=template.xlsx");

        // 返回响应实体
        return ResponseEntity.ok()
                .headers(headers)
                .contentLength(file.length())
                .contentType(MediaType.parseMediaType("application/octet-stream"))
                .body(resource);
    }

    @GetMapping("/downloadProvinceTemplateFile")
    public ResponseEntity<ByteArrayResource> downloadProvinceFile() throws IOException {

        // 从服务器读取文件
        File file = new File("src/main/resources/templates/template2.xlsx");
        byte[] data = Files.readAllBytes(file.toPath());
        ByteArrayResource resource = new ByteArrayResource(data);

        // 设置响应头信息
        HttpHeaders headers = new HttpHeaders();
        headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=template.xlsx");

        // 返回响应实体
        return ResponseEntity.ok()
                .headers(headers)
                .contentLength(file.length())
                .contentType(MediaType.parseMediaType("application/octet-stream"))
                .body(resource);
    }


    @PostMapping("/downloadStudentFile")
    @PermissionCheck(permissionPath = "/studentDataManagement/exportStudentFile")
    public void downloadStudentFile(@RequestBody StudentFileDownloadForm studentFileDownloadForm, HttpServletResponse response) throws IllegalAccessException, IOException, NoSuchFieldException {

        Map<String, String> conditions = studentFileDownloadForm.getConditions();
        List<Field> fieldList = studentFileDownloadForm.getFieldList();

        QueryWrapper<Student> studentQueryWrapper = new QueryWrapper<>();

        for (String key : conditions.keySet()) {
            if (!Objects.equals(conditions.get(key), "")) {
                studentQueryWrapper.eq(key, conditions.get(key));
            }
        }

        List<Student> students = studentService.list(studentQueryWrapper);

        List<OperateStudentForm> operateStudents = new ArrayList<>();

        for (Student student : students) {

            // 手机号解密
            if (!Objects.equals(student.getTelephone(), "")) {
                student.setTelephone(SecureUtil.aes(PHONE_ENCRYPT_KEY.getBytes()).decryptStr(student.getTelephone()));
            }

            OperateStudentForm operateStudent = new OperateStudentForm();

            StudentMoreInfoDTO studentMoreInfo = (StudentMoreInfoDTO) studentMoreInfoService.getStudentMoreInfoByMoreId(student.getMoreInfo()).getData();

            BeanUtil.copyProperties(student, operateStudent);
            BeanUtil.copyProperties(studentMoreInfo, operateStudent);

            operateStudents.add(operateStudent);
        }


        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        try (Workbook workbook = new XSSFWorkbook()) {

            Sheet sheet = workbook.createSheet("Students");

            Row headerRow = sheet.createRow(0);

            int headerCellColumn = 0;
            // 0,name   1,stuId
            Map<Integer, String> fieldMapping = new HashMap<>();

            // 设置xlsx第一行的header
            for (Field field : fieldList) {
                if (field.getClick()) {
                    headerRow.createCell(headerCellColumn).setCellValue(field.getLabel());
                    fieldMapping.put(headerCellColumn, field.getField());
                    headerCellColumn++;
                }
            }

            // 创建行
            int rowNum = 1;

            for (OperateStudentForm operateStudent : operateStudents) {
                Row row = sheet.createRow(rowNum);

                for (Integer column : fieldMapping.keySet()) {
                    String field = fieldMapping.get(column);

                    java.lang.reflect.Field declaredField = OperateStudentForm.class.getDeclaredField(field);
                    declaredField.setAccessible(true); // 如果该字段是私有的，需要设置可访问性


                    String value = "";

                    if (declaredField.get(operateStudent) != null) {
                        value = declaredField.get(operateStudent).toString();
                    }

                    if (field.equals("sex")) {
                        if (value.equals("1")) {
                            value = "男";
                        } else {
                            value = "女";
                        }
                    }

                    row.createCell(column).setCellValue(value);
                }
                rowNum++;
            }

            // Write workbook to output stream
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }

        // Set response headers
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=students.xlsx");

        ServletOutputStream servletOutputStream = response.getOutputStream();
        servletOutputStream.write(outputStream.toByteArray());
        servletOutputStream.flush();
        servletOutputStream.close();
    }


    @PostMapping("/downloadCollegeDataFile")
    @PermissionCheck(permissionPath = "/collegeEnrollment/exportCollegeEnrollmentData")
    public void downloadCollegeDataFile(@RequestBody List<CollegeEnrollmentDTOForm> collegeEnrollmentDTOS, HttpServletResponse response) throws IOException {

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        try (Workbook workbook = new XSSFWorkbook()) {

            Sheet sheet = workbook.createSheet("CollegeEnrollment");

            Row headerRow = sheet.createRow(0);

            // 设置xlsx第一行的header
            headerRow.createCell(0).setCellValue("学院名称");
            headerRow.createCell(1).setCellValue("学年");
            headerRow.createCell(2).setCellValue("计划招生数量");
            headerRow.createCell(3).setCellValue("实际招生数量");
            headerRow.createCell(4).setCellValue("第一志愿选择率");
            headerRow.createCell(5).setCellValue("第一志愿录取率");
            headerRow.createCell(6).setCellValue("覆盖省份数量");

            // 创建行
            int rowNum = 1;

            for (CollegeEnrollmentDTOForm collegeEnrollmentDTO : collegeEnrollmentDTOS) {
                Row row = sheet.createRow(rowNum);

                row.createCell(0).setCellValue(collegeEnrollmentDTO.getCollege());
                row.createCell(1).setCellValue(collegeEnrollmentDTO.getGrade());
                row.createCell(2).setCellValue(collegeEnrollmentDTO.getPlannedEnrollment());
                row.createCell(3).setCellValue(collegeEnrollmentDTO.getActualEnrollment());
                row.createCell(4).setCellValue(collegeEnrollmentDTO.getFirstChoiceRate());
                row.createCell(5).setCellValue(collegeEnrollmentDTO.getFirstAdmitRate());
                row.createCell(6).setCellValue(collegeEnrollmentDTO.getProvincesCovered());

                rowNum++;
            }

            // Write workbook to output stream
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }

        // Set response headers
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=students.xlsx");

        ServletOutputStream servletOutputStream = response.getOutputStream();
        servletOutputStream.write(outputStream.toByteArray());
        servletOutputStream.flush();
        servletOutputStream.close();
    }

    @PostMapping("/downloadCollegeMajorDataFile")
    @PermissionCheck(permissionPath = "/collegeEnrollment/exportCollegeEnrollmentData")
    public void downloadCollegeMajorDataFile(@RequestBody List<CollegeMajorEnrollmentForm> collegeMajorEnrollmentForm, HttpServletResponse response) throws IOException {

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        try (Workbook workbook = new XSSFWorkbook()) {

            Sheet sheet = workbook.createSheet("CollegeMajorEnrollmentForm");

            Row headerRow = sheet.createRow(0);

            // 设置xlsx第一行的header
            headerRow.createCell(0).setCellValue("专业名称");
            headerRow.createCell(1).setCellValue("学年");
            headerRow.createCell(2).setCellValue("专业平均分");
            headerRow.createCell(3).setCellValue("专业最高分");
            headerRow.createCell(4).setCellValue("专业最低分");
            headerRow.createCell(5).setCellValue("实际招生数");
            headerRow.createCell(6).setCellValue("第一志愿选择率");
            headerRow.createCell(7).setCellValue("第一志愿录取率");

            // 创建行
            int rowNum = 1;

            for (CollegeMajorEnrollmentForm collegeMajor : collegeMajorEnrollmentForm) {
                Row row = sheet.createRow(rowNum);

                row.createCell(0).setCellValue(collegeMajor.getMajor());
                row.createCell(1).setCellValue(collegeMajor.getGrade());
                row.createCell(2).setCellValue(collegeMajor.getAvgPoints());
                row.createCell(3).setCellValue(collegeMajor.getMaxPoints());
                row.createCell(4).setCellValue(collegeMajor.getMinPoints());
                row.createCell(5).setCellValue(collegeMajor.getEnrollmentNum());
                row.createCell(6).setCellValue(collegeMajor.getFirstChoiceRate());
                row.createCell(7).setCellValue(collegeMajor.getFirstAdmitRate());


                rowNum++;
            }

            // Write workbook to output stream
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }

        // Set response headers
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=students.xlsx");

        ServletOutputStream servletOutputStream = response.getOutputStream();
        servletOutputStream.write(outputStream.toByteArray());
        servletOutputStream.flush();
        servletOutputStream.close();
    }


    @PostMapping("/downloadProvinceDataFile")
    @PermissionCheck(permissionPath = "/enrollmentProvince/exportProvinceFile")
    public void downloadProvinceDataFile(@RequestBody List<CollegeMajorProvinceDTO> collegeMajorProvinceDTOs, HttpServletResponse response) throws IOException {

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        try (Workbook workbook = new XSSFWorkbook()) {

            Sheet sheet = workbook.createSheet("CollegeMajorProvince");

            Row headerRow = sheet.createRow(0);

            // 设置xlsx第一行的header
            headerRow.createCell(0).setCellValue("省份");
            headerRow.createCell(1).setCellValue("本科一批分数线");
            headerRow.createCell(2).setCellValue("学院平均分数");
            headerRow.createCell(3).setCellValue("专业平均分数");
            headerRow.createCell(4).setCellValue("学院最高分");
            headerRow.createCell(5).setCellValue("专业最高分");
            headerRow.createCell(6).setCellValue("学院最低分");
            headerRow.createCell(7).setCellValue("专业最低分");
            headerRow.createCell(8).setCellValue("学生数量");

            headerRow.createCell(12).setCellValue("学院");
            headerRow.createCell(13).setCellValue("学年");
            headerRow.createCell(14).setCellValue("专业");

            // 创建行
            int rowNum = 1;

            for (CollegeMajorProvinceDTO collegeMajorProvinceDTO : collegeMajorProvinceDTOs) {
                Row row = sheet.createRow(rowNum);

                setCellValueOrEmpty(row.createCell(0), collegeMajorProvinceDTO.getProvince());
                setCellValueOrEmpty(row.createCell(1), collegeMajorProvinceDTO.getAdmissionScore());
                setCellValueOrEmpty(row.createCell(2), collegeMajorProvinceDTO.getAverageScoreByCollege());
                setCellValueOrEmpty(row.createCell(3), collegeMajorProvinceDTO.getAverageScoreByMajor());
                setCellValueOrEmpty(row.createCell(4), collegeMajorProvinceDTO.getHighestScoreByCollege());
                setCellValueOrEmpty(row.createCell(5), collegeMajorProvinceDTO.getHighestScoreByMajor());
                setCellValueOrEmpty(row.createCell(6), collegeMajorProvinceDTO.getLowestScoreByCollege());
                setCellValueOrEmpty(row.createCell(7), collegeMajorProvinceDTO.getLowestScoreByMajor());
                setCellValueOrEmpty(row.createCell(8), collegeMajorProvinceDTO.getStudentNum());

                setCellValueOrEmpty(row.createCell(12), collegeMajorProvinceDTO.getCollege());
                setCellValueOrEmpty(row.createCell(13), collegeMajorProvinceDTO.getGrade());
                setCellValueOrEmpty(row.createCell(14), collegeMajorProvinceDTO.getMajor());

                rowNum++;
            }


            // Write workbook to output stream
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }

        // Set response headers
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=students.xlsx");

        ServletOutputStream servletOutputStream = response.getOutputStream();
        servletOutputStream.write(outputStream.toByteArray());
        servletOutputStream.flush();
        servletOutputStream.close();
    }

    public void setCellValueOrEmpty(Cell cell, Object value) {
        if (value != null) {
            cell.setCellValue(value.toString());
        } else {
            cell.setCellValue("");
        }
    }

}
